﻿using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
using WPFExcelReport.Helpers;
namespace INTEX.DataBase.Tools
{
    public partial class INTEXDBC
    {
        public void MapICC_STOCK(ref DataRow dwLocal, DataRow dwData)
        {
            BlindMap(ref dwLocal, dwData);
        }
        public void SaveICC_STOCK(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            DataSet Result = new DataSet();
            DataTable tbLocal = null;
            DataRow dwLocal = null;
            bool flgExist = false;
            Result = QueryICC_STOCK(dwData);
            if (Result.Tables.Count > 0)
            {
                tbLocal = Result.Tables[0];
                if (tbLocal.Rows.Count > 0)
                {
                    flgExist = true;
                    dwLocal = tbLocal.Rows[0];
                }
                else
                {
                    dwLocal = tbLocal.NewRow();
                }
            }

            MapICC_STOCK(ref dwLocal, dwData);

            if (!flgExist)
            {
                dwLocal["ROWNID"] = GetSeq();
                InsertICC_STOCK(dwLocal, 1);
            }
            UpdateICC_STOCK(dwLocal);
        }
        public void InsertICC_STOCK(DataRow dwData, int option = 1)
        {

            /*  
             DROP TABLE  ICC_STOCK
GO
DELETE FROM ICC_STOCK
GO
CREATE TABLE ICC_STOCK
AS
INSERT INTO ICC_STOCK
SELECT MANDANT,LO,LB,MDATE,SPPART,ZWPART,ART,VAR,FARBE,FINISH,0 AS STOCKQTY,
0 AS BS00,0 AS BS01,0 AS BS02,0 AS BS03,0 AS BS04,0 AS BS05,0 AS BS06,0 AS BS07,0 AS BS08,0 AS BS09,
0 AS BS10,0 AS BS11,0 AS BS12,0 AS BS13,0 AS BS14,0 AS BS15,0 AS BS16,0 AS BS17,0 AS BS18,0 AS BS19,
0 AS BS20,0 AS BS21,0 AS BS22,0 AS BS23,0 AS BS24,0 AS BS25,0 AS BS26,0 AS BS27,0 AS BS28,0 AS BS29,
0 AS BS30,0 AS BS31,0 AS BS32,0 AS BS33,0 AS BS34,0 AS BS35,0 AS BS36,0 AS BS37,0 AS BS38,0 AS BS39,
0 AS BS40,0 AS BS41,0 AS BS42,0 AS BS43,0 AS BS44,0 AS BS45,0 AS BS46,0 AS BS47,0 AS BS48,0 AS BS49,
0 AS BS50,0 AS BS51,0 AS BS52,0 AS BS53,0 AS BS54,0 AS BS55,0 AS BS56,0 AS BS57,0 AS BS58,0 AS BS59,
0 AS BS60,0 AS BS61,0 AS BS62,0 AS BS63,0 AS BS64,0 AS BS65,0 AS BS66,0 AS BS67,0 AS BS68,0 AS BS69,
0 AS BS70,0 AS BS71,0 AS BS72,0 AS BS73,0 AS BS74,0 AS BS75,0 AS BS76,0 AS BS77,0 AS BS78,0 AS BS79,
0 AS BS80,0 AS BS81,0 AS BS82,0 AS BS83,0 AS BS84,0 AS BS85,0 AS BS86,0 AS BS87,0 AS BS88,0 AS BS89,
0 AS BS90,0 AS BS91,0 AS BS92,0 AS BS93,0 AS BS94,0 AS BS95,0 AS BS96,0 AS BS97,0 AS BS98,0 AS BS99
FROM ICC_STOCKCHG W WHERE NOT EXISTS
(SELECT 1 FROM ICC_STOCK X WHERE X.MANDANT=W.MANDANT AND X.LO=W.LO AND X.LB=W.LB AND X.MDATE=W.MDATE
AND X.SPPART=W.SPPART AND X.ZWPART=W.ZWPART AND X.ART=W.ART AND X.VAR=W.VAR AND X.FARBE=W.FARBE AND X.FINISH=W.FINISH)
                 */
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "INSERT INTO ICC_STOCK "
                    + "SELECT DISTINCT GPMANDANT,GLLO,GLLB,:MDATE,GPFBPART,GPSPPART,GPZWPART,GPBEM1,GPGART,GPGVAR,GPGFARBE,GPGFARBE,0 AS STOCKQTY,"
                    + "0 AS BS00,0 AS BS01,0 AS BS02,0 AS BS03,0 AS BS04,0 AS BS05,0 AS BS06,0 AS BS07,0 AS BS08,0 AS BS09,"
                    + "0 AS BS10,0 AS BS11,0 AS BS12,0 AS BS13,0 AS BS14,0 AS BS15,0 AS BS16,0 AS BS17,0 AS BS18,0 AS BS19,"
                    + "0 AS BS20,0 AS BS21,0 AS BS22,0 AS BS23,0 AS BS24,0 AS BS25,0 AS BS26,0 AS BS27,0 AS BS28,0 AS BS29,"
                    + "0 AS BS30,0 AS BS31,0 AS BS32,0 AS BS33,0 AS BS34,0 AS BS35,0 AS BS36,0 AS BS37,0 AS BS38,0 AS BS39,"
                    + "0 AS BS40,0 AS BS41,0 AS BS42,0 AS BS43,0 AS BS44,0 AS BS45,0 AS BS46,0 AS BS47,0 AS BS48,0 AS BS49,"
                    + "0 AS BS50,0 AS BS51,0 AS BS52,0 AS BS53,0 AS BS54,0 AS BS55,0 AS BS56,0 AS BS57,0 AS BS58,0 AS BS59,"
                    + "0 AS BS60,0 AS BS61,0 AS BS62,0 AS BS63,0 AS BS64,0 AS BS65,0 AS BS66,0 AS BS67,0 AS BS68,0 AS BS69,"
                    + "0 AS BS70,0 AS BS71,0 AS BS72,0 AS BS73,0 AS BS74,0 AS BS75,0 AS BS76,0 AS BS77,0 AS BS78,0 AS BS79,"
                    + "0 AS BS80,0 AS BS81,0 AS BS82,0 AS BS83,0 AS BS84,0 AS BS85,0 AS BS86,0 AS BS87,0 AS BS88,0 AS BS89,"
                    + "0 AS BS90,0 AS BS91,0 AS BS92,0 AS BS93,0 AS BS94,0 AS BS95,0 AS BS96,0 AS BS97,0 AS BS98,0 AS BS99"
                    + " FROM GLLG JOIN GPDAT W ON GLPARTIE=GPPARTIE  WHERE NOT EXISTS"
                    + " (SELECT 1 FROM ICC_STOCK X WHERE X.MDATE=:MDATE AND X.MANDANT=W.GPMANDANT AND X.LO=GLLO AND X.LB=GLLB  "
                    + " AND X.FBPART=W.GPFBPART AND X.BEM1=W.GPBEM1 AND X.SPPART=W.GPSPPART AND X.ZWPART=W.GPZWPART AND X.ART=W.GPGART AND X.VAR=W.GPGVAR AND X.FARBE=W.GPGFARBE ) ";
                    or1.Parameters.Add("MDATE", OracleType.NVarChar).Value = dwData["MDATE"].ToString();
                    break;
                case 2:
                    or1.CommandText = "INSERT INTO ICC_STOCK "
                    + "SELECT DISTINCT MANDANT,LO,LB,MDATE,FBPART,SPPART,ZWPART,BEM1,ART,VAR,FARBE,FINISH,0 AS STOCKQTY,"
                    + "0 AS BS00,0 AS BS01,0 AS BS02,0 AS BS03,0 AS BS04,0 AS BS05,0 AS BS06,0 AS BS07,0 AS BS08,0 AS BS09,"
                    + "0 AS BS10,0 AS BS11,0 AS BS12,0 AS BS13,0 AS BS14,0 AS BS15,0 AS BS16,0 AS BS17,0 AS BS18,0 AS BS19,"
                    + "0 AS BS20,0 AS BS21,0 AS BS22,0 AS BS23,0 AS BS24,0 AS BS25,0 AS BS26,0 AS BS27,0 AS BS28,0 AS BS29,"
                    + "0 AS BS30,0 AS BS31,0 AS BS32,0 AS BS33,0 AS BS34,0 AS BS35,0 AS BS36,0 AS BS37,0 AS BS38,0 AS BS39,"
                    + "0 AS BS40,0 AS BS41,0 AS BS42,0 AS BS43,0 AS BS44,0 AS BS45,0 AS BS46,0 AS BS47,0 AS BS48,0 AS BS49,"
                    + "0 AS BS50,0 AS BS51,0 AS BS52,0 AS BS53,0 AS BS54,0 AS BS55,0 AS BS56,0 AS BS57,0 AS BS58,0 AS BS59,"
                    + "0 AS BS60,0 AS BS61,0 AS BS62,0 AS BS63,0 AS BS64,0 AS BS65,0 AS BS66,0 AS BS67,0 AS BS68,0 AS BS69,"
                    + "0 AS BS70,0 AS BS71,0 AS BS72,0 AS BS73,0 AS BS74,0 AS BS75,0 AS BS76,0 AS BS77,0 AS BS78,0 AS BS79,"
                    + "0 AS BS80,0 AS BS81,0 AS BS82,0 AS BS83,0 AS BS84,0 AS BS85,0 AS BS86,0 AS BS87,0 AS BS88,0 AS BS89,"
                    + "0 AS BS90,0 AS BS91,0 AS BS92,0 AS BS93,0 AS BS94,0 AS BS95,0 AS BS96,0 AS BS97,0 AS BS98,0 AS BS99"
                    + " FROM ICC_STOCKCHG W WHERE NOT EXISTS"
                    + " (SELECT 1 FROM ICC_STOCK X WHERE X.MANDANT=W.MANDANT AND X.LO=W.LO AND X.LB=W.LB AND X.MDATE=W.MDATE"
                    + " AND X.FBPART=W.FBPART AND X.BEM1=W.BEM1 AND X.SPPART=W.SPPART AND X.ZWPART=W.ZWPART AND X.ART=W.ART AND X.VAR=W.VAR AND X.FARBE=W.FARBE) ";
                    break;
                case 3:
                    or1.CommandText = "INSERT INTO ICC_STOCK "
                      + "SELECT DISTINCT GPMANDANT,GLLO,GLLB,ICCDAT,GPFBPART,GPSPPART,GPZWPART,GPBEM1,GPGART,GPGVAR,GPGFARBE,GPGFARBE,0 AS STOCKQTY,"
                      + "0 AS BS00,0 AS BS01,0 AS BS02,0 AS BS03,0 AS BS04,0 AS BS05,0 AS BS06,0 AS BS07,0 AS BS08,0 AS BS09,"
                      + "0 AS BS10,0 AS BS11,0 AS BS12,0 AS BS13,0 AS BS14,0 AS BS15,0 AS BS16,0 AS BS17,0 AS BS18,0 AS BS19,"
                      + "0 AS BS20,0 AS BS21,0 AS BS22,0 AS BS23,0 AS BS24,0 AS BS25,0 AS BS26,0 AS BS27,0 AS BS28,0 AS BS29,"
                      + "0 AS BS30,0 AS BS31,0 AS BS32,0 AS BS33,0 AS BS34,0 AS BS35,0 AS BS36,0 AS BS37,0 AS BS38,0 AS BS39,"
                      + "0 AS BS40,0 AS BS41,0 AS BS42,0 AS BS43,0 AS BS44,0 AS BS45,0 AS BS46,0 AS BS47,0 AS BS48,0 AS BS49,"
                      + "0 AS BS50,0 AS BS51,0 AS BS52,0 AS BS53,0 AS BS54,0 AS BS55,0 AS BS56,0 AS BS57,0 AS BS58,0 AS BS59,"
                      + "0 AS BS60,0 AS BS61,0 AS BS62,0 AS BS63,0 AS BS64,0 AS BS65,0 AS BS66,0 AS BS67,0 AS BS68,0 AS BS69,"
                      + "0 AS BS70,0 AS BS71,0 AS BS72,0 AS BS73,0 AS BS74,0 AS BS75,0 AS BS76,0 AS BS77,0 AS BS78,0 AS BS79,"
                      + "0 AS BS80,0 AS BS81,0 AS BS82,0 AS BS83,0 AS BS84,0 AS BS85,0 AS BS86,0 AS BS87,0 AS BS88,0 AS BS89,"
                      + "0 AS BS90,0 AS BS91,0 AS BS92,0 AS BS93,0 AS BS94,0 AS BS95,0 AS BS96,0 AS BS97,0 AS BS98,0 AS BS99"
                      + " FROM ICC_GLLG JOIN GPDAT W ON GLPARTIE=GPPARTIE  WHERE NOT EXISTS"
                      + " (SELECT 1 FROM ICC_STOCK X WHERE X.MDATE=ICCDAT AND X.MANDANT=W.GPMANDANT AND X.LO=GLLO AND X.LB=GLLB  "
                      + " AND X.FBPART=W.GPFBPART AND X.BEM1=W.GPBEM1 AND X.SPPART=W.GPSPPART AND X.ZWPART=W.GPZWPART AND X.ART=W.GPGART AND X.VAR=W.GPGVAR AND X.FARBE=W.GPGFARBE ) ";
                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
        }
        public void DeleteICC_STOCK(DataRow dwData, int option = 1)
        {

            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "DELETE FROM ICC_STOCK"
                 + " WHERE GABBELEG<=:GABBELEG";
                    or1.Parameters.Add("GABBELEG", OracleType.NVarChar).Value = dwData["GABBELEG"].ToString();
                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
        }
        public DataSet QueryICC_STOCK(DataRow dwData, int option = 1)
        {
            DataSet Return;
            DataSet Result = new DataSet();
            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "SELECT  PATTEN,M.*  FROM ICC_STOCK M LEFT JOIN ICC_GLLGEX G ON M.MDATE=ICCDAT AND GPMANDANT=MANDANT AND GLLO=LO AND GLLB=LB AND GPGART=ART AND GPGVAR=VAR AND GPGFARBE=FARBE    AND  GPSPPART=SPPART AND GPZWPART=ZWPART AND GPFBPART=FBPART AND  GPBEM1=BEM1 WHERE MDATE=:MDATE "
                    + " AND ART LIKE '"+dwData["ART"].ToString().Trim()+"%' AND LO LIKE '%" + dwData["LO"].ToString().Trim() + "%' AND LB  LIKE '%" + dwData["LB"].ToString().Trim() + "%' AND ART LIKE '" + dwData["ART"].ToString().Trim() + "%'"; ;
                    or1.Parameters.Add("MDATE", OracleType.NVarChar).Value = dwData["MDATE"].ToString();
                    //or1.Parameters.Add("LO", OracleType.NVarChar).Value = dwData["LO"].ToString()
                    //or1.Parameters.Add("LB", OracleType.NVarChar).Value = dwData["LB"].ToString();
                    break;
                case 3:
                    or1.CommandText = "SELECT *  FROM ICC_STOCK WHERE MDATE=:MDATE AND LO=:LO AND LB=:LB AND ART LIKE '" + dwData["ART"].ToString().Trim() + "%'"
                    + " GROUP BY LO,LO,ART,VAR,FARBE,";
                    or1.Parameters.Add("MDATE", OracleType.NVarChar).Value = dwData["MDATE"].ToString();
                    or1.Parameters.Add("LO", OracleType.NVarChar).Value = dwData["LO"].ToString();
                    or1.Parameters.Add("LB", OracleType.NVarChar).Value = dwData["LB"].ToString();
                    break;
                case 2:
                    or1.CommandText = "SELECT MAX(GABBELEG) AS GABBELEG  FROM ICC_STOCK WHERE GABBUCHDAT<=:GABBUCHDAT"; ;
                    or1.Parameters.Add("GABBUCHDAT", OracleType.NVarChar).Value = dwData["GABBUCHDAT"].ToString();
                    break;
                case 4:
                    string ICCDAT = dwData["MDATE"].ToString();
                    string MDATE = Helper.GetDateAddDays(ICCDAT, 0 - 1);
                    or1.CommandText = "SELECT  PATTEN,M.*  FROM ICC_STOCK M LEFT JOIN ICC_GLLGEX G ON M.MDATE='" + MDATE + "' AND ICCDAT='" + ICCDAT + "' AND GPMANDANT=MANDANT AND GLLO=LO AND GLLB=LB AND GPGART=ART AND GPGVAR=VAR AND GPGFARBE=FARBE    AND  GPSPPART=SPPART AND GPZWPART=ZWPART AND GPFBPART=FBPART AND  "
                    + "GPBEM1=BEM1 WHERE MDATE='" + MDATE + "' AND ART LIKE '"+dwData["ART"].ToString().Trim()+"%' AND LO LIKE '%" + dwData["LO"].ToString().Trim() + "%' AND LB  LIKE '%" + dwData["LB"].ToString().Trim() + "%' AND ART LIKE '" + dwData["ART"].ToString().Trim() + "%'"; ;

                    //or1.Parameters.Add("LO", OracleType.NVarChar).Value = dwData["LO"].ToString();
                    //                    or1.Parameters.Add("LB", OracleType.NVarChar).Value = dwData["LB"].ToString();
                    break;
                default:
                    break;
            }
            Result = Connect2Proxy(or1);
            if (Result.Tables.Count > 0)
                Result.Tables[0].TableName = "ICC_STOCK";
            Return = Result;
            return Return;
        }
        public void UpdateICC_STOCK(DataRow dwData, int option = 1)
        {
            OracleCommand or1;
            DataSet Result = new DataSet();
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "UPDATE ICC_STOCK X SET(BS00,BS01,BS02,BS03,BS04,BS05,BS06,BS07,BS08,BS09,BS10,BS11,BS12,BS13,BS14,BS15,BS16,BS17,BS18,BS19,BS20,BS21,BS22,BS23,BS24,BS25,BS26,BS27,BS28,BS29,BS30,BS31,BS32,BS33,BS34,BS35,BS36,BS37,BS38,BS39,BS40,BS41,BS42,BS43,BS44,BS45,BS46,BS47,BS48,BS49,BS50,BS51,BS52,BS53,BS54,BS55,BS56,BS57,BS58,BS59,BS60,BS61,BS62,BS63,BS64,BS65,BS66,BS67,BS68,BS69,BS70,BS71,BS72,BS73,BS74,BS75,BS76,BS77,BS78,BS79,BS80,BS81,BS82,BS83,BS84,BS85,BS86,BS87,BS88,BS89,BS90,BS91,BS92,BS93,BS94,BS95,BS96,BS97,BS98,BS99)=(select"
                    + " sum(decode(BS,'00',BSQTY,null)) as BS00,sum(decode(BS,'01',BSQTY,null)) as BS01,sum(decode(BS,'02',BSQTY,null)) as BS02,sum(decode(BS,'03',BSQTY,null)) as BS03,sum(decode(BS,'04',BSQTY,null)) as BS04,sum(decode(BS,'05',BSQTY,null)) as BS05,sum(decode(BS,'06',BSQTY,null)) as BS06,sum(decode(BS,'07',BSQTY,null)) as BS07,sum(decode(BS,'08',BSQTY,null)) as BS08,sum(decode(BS,'09',BSQTY,null)) as BS09,"
                    + "sum(decode(BS,'10',BSQTY,null)) as BS10,sum(decode(BS,'11',BSQTY,null)) as BS11,sum(decode(BS,'12',BSQTY,null)) as BS12,sum(decode(BS,'13',BSQTY,null)) as BS13,sum(decode(BS,'14',BSQTY,null)) as BS14,sum(decode(BS,'15',BSQTY,null)) as BS15,sum(decode(BS,'16',BSQTY,null)) as BS16,sum(decode(BS,'17',BSQTY,null)) as BS17,sum(decode(BS,'18',BSQTY,null)) as BS18,sum(decode(BS,'19',BSQTY,null)) as BS19,"
                    + "sum(decode(BS,'20',BSQTY,null)) as BS20,sum(decode(BS,'21',BSQTY,null)) as BS21,sum(decode(BS,'22',BSQTY,null)) as BS22,sum(decode(BS,'23',BSQTY,null)) as BS23,sum(decode(BS,'24',BSQTY,null)) as BS24,sum(decode(BS,'25',BSQTY,null)) as BS25,sum(decode(BS,'26',BSQTY,null)) as BS26,sum(decode(BS,'27',BSQTY,null)) as BS27,sum(decode(BS,'28',BSQTY,null)) as BS28,sum(decode(BS,'29',BSQTY,null)) as BS29,"
                    + "sum(decode(BS,'30',BSQTY,null)) as BS30,sum(decode(BS,'31',BSQTY,null)) as BS31,sum(decode(BS,'32',BSQTY,null)) as BS32,sum(decode(BS,'33',BSQTY,null)) as BS33,sum(decode(BS,'34',BSQTY,null)) as BS34,sum(decode(BS,'35',BSQTY,null)) as BS35,sum(decode(BS,'36',BSQTY,null)) as BS36,sum(decode(BS,'37',BSQTY,null)) as BS37,sum(decode(BS,'38',BSQTY,null)) as BS38,sum(decode(BS,'39',BSQTY,null)) as BS39,"
                    + "sum(decode(BS,'40',BSQTY,null)) as BS40,sum(decode(BS,'41',BSQTY,null)) as BS41,sum(decode(BS,'42',BSQTY,null)) as BS42,sum(decode(BS,'43',BSQTY,null)) as BS43,sum(decode(BS,'44',BSQTY,null)) as BS44,sum(decode(BS,'45',BSQTY,null)) as BS45,sum(decode(BS,'46',BSQTY,null)) as BS46,sum(decode(BS,'47',BSQTY,null)) as BS47,sum(decode(BS,'48',BSQTY,null)) as BS48,sum(decode(BS,'49',BSQTY,null)) as BS49,"
                    + "sum(decode(BS,'50',BSQTY,null)) as BS50,sum(decode(BS,'51',BSQTY,null)) as BS51,sum(decode(BS,'52',BSQTY,null)) as BS52,sum(decode(BS,'53',BSQTY,null)) as BS53,sum(decode(BS,'54',BSQTY,null)) as BS54,sum(decode(BS,'55',BSQTY,null)) as BS55,sum(decode(BS,'56',BSQTY,null)) as BS56,sum(decode(BS,'57',BSQTY,null)) as BS57,sum(decode(BS,'58',BSQTY,null)) as BS58,sum(decode(BS,'59',BSQTY,null)) as BS59,"
                    + "sum(decode(BS,'60',BSQTY,null)) as BS60,sum(decode(BS,'61',BSQTY,null)) as BS61,sum(decode(BS,'62',BSQTY,null)) as BS62,sum(decode(BS,'63',BSQTY,null)) as BS63,sum(decode(BS,'64',BSQTY,null)) as BS64,sum(decode(BS,'65',BSQTY,null)) as BS65,sum(decode(BS,'66',BSQTY,null)) as BS66,sum(decode(BS,'67',BSQTY,null)) as BS67,sum(decode(BS,'68',BSQTY,null)) as BS68,sum(decode(BS,'69',BSQTY,null)) as BS69,"
                    + "sum(decode(BS,'70',BSQTY,null)) as BS70,sum(decode(BS,'71',BSQTY,null)) as BS71,sum(decode(BS,'72',BSQTY,null)) as BS72,sum(decode(BS,'73',BSQTY,null)) as BS73,sum(decode(BS,'74',BSQTY,null)) as BS74,sum(decode(BS,'75',BSQTY,null)) as BS75,sum(decode(BS,'76',BSQTY,null)) as BS76,sum(decode(BS,'77',BSQTY,null)) as BS77,sum(decode(BS,'78',BSQTY,null)) as BS78,sum(decode(BS,'79',BSQTY,null)) as BS79,"
                    + "sum(decode(BS,'80',BSQTY,null)) as BS80,sum(decode(BS,'81',BSQTY,null)) as BS81,sum(decode(BS,'82',BSQTY,null)) as BS82,sum(decode(BS,'83',BSQTY,null)) as BS83,sum(decode(BS,'84',BSQTY,null)) as BS84,sum(decode(BS,'85',BSQTY,null)) as BS85,sum(decode(BS,'86',BSQTY,null)) as BS86,sum(decode(BS,'87',BSQTY,null)) as BS87,sum(decode(BS,'88',BSQTY,null)) as BS88,sum(decode(BS,'89',BSQTY,null)) as BS89,"
                    + "sum(decode(BS,'90',BSQTY,null)) as BS90,sum(decode(BS,'91',BSQTY,null)) as BS91,sum(decode(BS,'92',BSQTY,null)) as BS92,sum(decode(BS,'93',BSQTY,null)) as BS93,sum(decode(BS,'94',BSQTY,null)) as BS94,sum(decode(BS,'95',BSQTY,null)) as BS95,sum(decode(BS,'96',BSQTY,null)) as BS96,sum(decode(BS,'97',BSQTY,null)) as BS97,sum(decode(BS,'98',BSQTY,null)) as BS98,sum(decode(BS,'99',BSQTY,null)) as BS99"
                    + " from ICC_STOCKCHG Y WHERE X.MANDANT=Y.MANDANT AND X.LO=Y.LO AND X.LB=Y.LB AND X.MDATE=Y.MDATE AND X.SPPART=Y.SPPART AND X.ZWPART=Y.ZWPART AND X.ART=Y.ART AND X.VAR=Y.VAR AND X.FARBE=Y.FARBE AND X.FINISH=Y.FINISH group by MANDANT,LO,LB,MDATE,SPPART,ZWPART,ART,VAR,FARBE,FINISH) WHERE EXISTS ( SELECT 1 FROM ICC_STOCKCHG Y WHERE X.MANDANT=Y.MANDANT AND X.LO=Y.LO AND X.LB=Y.LB AND X.MDATE=Y.MDATE AND X.SPPART=Y.SPPART AND X.ZWPART=Y.ZWPART AND X.ART=Y.ART AND X.VAR=Y.VAR AND X.FARBE=Y.FARBE AND X.FINISH=Y.FINISH )";
                    break;
                case 2:
                    or1.CommandText = "UPDATE ICC_STOCK W SET (STOCKQTY)=( SELECT STOCK FROM ( SELECT GPMANDANT,GPSPPART,GPZWPART,GPFBPART,GPBEM1,GLLO,GLLB,GPGART,GPGVAR,GPGFARBE,SUM(GLBSTKG*0.001) AS STOCK FROM GLLG X JOIN GPDAT Y ON GLPARTIE=GPPARTIE GROUP BY GPMANDANT,GPSPPART,GPZWPART,GPFBPART,GPBEM1,GLLO,GLLB,GPGART,GPGVAR,GPGFARBE) Z WHERE W.MANDANT=Z.GPMANDANT AND W.LO=GLLO AND W.LB=GLLB AND ART=GPGART AND VAR=GPGVAR AND FARBE=GPGFARBE AND FBPART=GPFBPART AND SPPART=GPSPPART AND ZWPART=GPZWPART AND BEM1=GPBEM1)"
                + "   WHERE  W.MDATE=:MDATE";
                    or1.Parameters.Add("MDATE", OracleType.NVarChar).Value = dwData["MDATE"].ToString();
                    break;
                case 3:
                    or1.CommandText = "UPDATE ICC_STOCK W SET (BS00) ="
                    + " (SELECT STOCK FROM (SELECT GPMANDANT, GPSPPART, GPZWPART, GPFBPART, GPBEM1, GLLO, GLLB, GPGART, GPGVAR, GPGFARBE, SUM(GLBSTKG * 0.001) AS STOCK, ICCDAT FROM ICC_GLLG X JOIN GPDAT Y ON GLPARTIE = GPPARTIE "
                    + " WHERE ICCDAT = :MDATE  GROUP BY GPMANDANT, GPSPPART, GPZWPART, GPFBPART, GPBEM1, GLLO, GLLB, GPGART, GPGVAR, GPGFARBE, ICCDAT) Z"
                    + " WHERE ICCDAT = MDATE AND W.MANDANT = Z.GPMANDANT AND W.LO = GLLO AND W.LB = GLLB AND W.ART=GPGART AND W.VAR=GPGVAR AND W.FARBE=GPGFARBE AND  FBPART = GPFBPART AND SPPART = GPSPPART AND ZWPART = GPZWPART AND BEM1 = GPBEM1) WHERE EXISTS( SELECT 1 FROM (SELECT GPMANDANT, GPSPPART, GPZWPART, GPFBPART, GPBEM1, GLLO, GLLB, GPGART, GPGVAR, GPGFARBE, SUM(GLBSTKG * 0.001) AS STOCK, ICCDAT FROM ICC_GLLG X JOIN GPDAT Y ON GLPARTIE = GPPARTIE "
                    + " WHERE ICCDAT = :MDATE  GROUP BY GPMANDANT, GPSPPART, GPZWPART, GPFBPART, GPBEM1, GLLO, GLLB, GPGART, GPGVAR, GPGFARBE, ICCDAT) Z WHERE ICCDAT = MDATE AND W.MANDANT = Z.GPMANDANT AND W.LO = GLLO AND W.LB = GLLB AND ART=GPGART AND VAR=GPGVAR AND FARBE=GPGFARBE AND  FBPART = GPFBPART AND SPPART = GPSPPART AND ZWPART = GPZWPART AND BEM1 = GPBEM1)";
                    or1.Parameters.Add("MDATE", OracleType.NVarChar).Value = dwData["MDATE"].ToString();
                    //or1.Parameters.Add("GABBUCHDAT", OracleType.NVarChar).Value = dwData["GABBUCHDAT"].ToString();
                    break;
                case 4:
                    or1.CommandText = "UPDATE ICC_STOCK W SET (STOCKQTY) =(SELECT STOCK FROM (SELECT GPMANDANT, GPSPPART,GPZWPART, GPFBPART, GPBEM1, GLLO, GLLB, GPGART, GPGVAR,GPGFARBE, SUM(GLBSTKG * 0.001) AS STOCK, ICCDAT FROM ICC_GLLG X JOIN GPDAT Y ON GLPARTIE = GPPARTIE "
                    + " WHERE ICCDAT =:GABBUCHDAT GROUP BY GPMANDANT, GPSPPART, GPZWPART, GPFBPART,GPBEM1, GLLO, GLLB, GPGART, GPGVAR, GPGFARBE, ICCDAT) Z WHERE ICCDAT = :GABBUCHDAT AND W.MANDANT = Z.GPMANDANT AND W.LO = GLLO AND W.LB = GLLB AND W.ART=GPGART AND W.VAR=GPGVAR AND W.FARBE=GPGFARBE AND FBPART = GPFBPART AND SPPART = GPSPPART AND ZWPART = GPZWPART AND BEM1 = GPBEM1) "
                    + " WHERE W.MDATE=:MDATE AND EXISTS( SELECT 1 FROM (SELECT GPMANDANT, GPSPPART, GPZWPART,GPFBPART, GPBEM1, GLLO, GLLB, GPGART, GPGVAR, GPGFARBE, SUM(GLBSTKG * 0.001) AS STOCK,ICCDAT FROM ICC_GLLG X JOIN GPDAT Y ON GLPARTIE = GPPARTIE"
                    + " WHERE ICCDAT = :GABBUCHDAT GROUP BY GPMANDANT, GPSPPART, GPZWPART, GPFBPART, GPBEM1, GLLO, GLLB, GPGART, GPGVAR, GPGFARBE, ICCDAT) Z     WHERE ICCDAT = :GABBUCHDAT AND W.MANDANT = Z.GPMANDANT AND W.LO = GLLO AND W.LB = GLLB AND ART=GPGART AND VAR=GPGVAR AND FARBE=GPGFARBE AND FBPART = GPFBPART AND SPPART = GPSPPART AND ZWPART = GPZWPART AND BEM1 = GPBEM1)";
                    or1.Parameters.Add("MDATE", OracleType.NVarChar).Value = dwData["MDATE"].ToString();//today
                    or1.Parameters.Add("GABBUCHDAT", OracleType.NVarChar).Value = dwData["GABBUCHDAT"].ToString(); //tomorrow
                    break;
                case 5:
                    or1.CommandText = "UPDATE ICC_STOCK W SET STOCKQTY=0,BS00=0 WHERE MDATE=:MDATE"; ;
                    or1.Parameters.Add("MDATE", OracleType.NVarChar).Value = dwData["MDATE"].ToString();//today
                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
        }
    }
}
